<!DOCTYPE html>
<html lang="en"><head>
    <title>CZH-DEV BLOG</title>
    <meta content="text/html;charset=utf-8" http-equiv="Content-Type">
    <meta content="utf-8" http-equiv="encoding">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="format-detection" content="telephone=no" />
    <meta name="theme-color" content="#000084" />
    <link rel="icon" href="https://czh-dev.gitee.io/czh-blog.gitee.io//favicon.ico">
    <link rel="canonical" href="https://czh-dev.gitee.io/czh-blog.gitee.io/">
    
    
</head>
<body>
<nav class="navbar navbar-inverse navbar-fixed-top">
    <div class="navbar-inner">
        <div class="container">
            <button type="button" class="btn btn-navbar" data-toggle="collapse" data-target=".nav-collapse"></button>
            <a class="brand" href="https://czh-dev.gitee.io/czh-blog.gitee.io/">CZH-DEV BLOG</a>
            <div class="nav-collapse collapse">
                <ul class="nav">
                    
                    
                        
                            <li>
                                <a href="/czh-blog.gitee.io/about/">
                                    
                                    <span>About</span>
                                </a>
                            </li>
                        
                    
                        
                            <li>
                                <a href="/czh-blog.gitee.io/post/">
                                    
                                    <span>All posts</span>
                                </a>
                            </li>
                        
                    
                        
                            <li>
                                <a href="/czh-blog.gitee.io/ebook/">
                                    
                                    <span>Resource</span>
                                </a>
                            </li>
                        
                    
                </ul>
            </div>
        </div>
    </div>
</nav><div id="content" class="container">
<div style="display: flex;">
  <div class="row-fluid navmargin">
    <div class="page-header">
      <h1>操作Excel - Sat, Feb 11, 2023</h1>
    </div>
    <p class="lead"></p>
    <p>@<a href="%E5%AF%BC%E5%85%A5Excel%E8%A1%A8%E6%A0%BC%E6%95%B0%E6%8D%AE%E5%88%B0%E6%95%B0%E6%8D%AE%E5%BA%93/%E4%BB%8E%E6%95%B0%E6%8D%AE%E5%BA%93%E5%AF%BC%E5%87%BA">TOC</a></p>
<h1 id="springboot-导入导入excel">SpringBoot-导入/导入Excel</h1>
<p>所谓导出，就是把数据库中表的数据导出到 excel文件中；导入，就是把 excel文件中的数据导入到数据库表中。这功能类似数据库的导入导出功能，只是区别在于这个操作者是普通用户，是在浏览器操作的，使用excel更易于阅读。</p>
<h2 id="apache-poi">Apache POI</h2>
<p>Apache POI 是 Apache 软件基金会的开放源码函式库，POI 提供 API 给 Java 程序对 Microsoft Office 格式档案读和写的功能。</p>
<ul>
<li>poi中关于excel的概念</li>
</ul>
<blockquote>
<p>Workbook（对应为一个excel）</p>
<p>Sheet（excel中的表）</p>
<p>Row（表中的行）</p>
<p>Column（表中的列）</p>
<p>Cell（表中的单元格，由行号和列号组成）</p>
</blockquote>
<ul>
<li>添加依赖</li>
</ul>
<blockquote>
<p>该段代码可在：下文<code>完整代码——pom.xml</code>中找到</p>
</blockquote>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-xml" data-lang="xml"><span style="display:flex;"><span><span style="color:#f92672">&lt;dependency&gt;</span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">&lt;groupId&gt;</span>org.apache.poi<span style="color:#f92672">&lt;/groupId&gt;</span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">&lt;artifactId&gt;</span>poi-ooxml<span style="color:#f92672">&lt;/artifactId&gt;</span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">&lt;version&gt;</span>4.1.2<span style="color:#f92672">&lt;/version&gt;</span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">&lt;scope&gt;</span>compile<span style="color:#f92672">&lt;/scope&gt;</span>
</span></span><span style="display:flex;"><span><span style="color:#f92672">&lt;/dependency&gt;</span>
</span></span></code></pre></div><h2 id="实现导入功能">实现导入功能</h2>
<p><!-- raw HTML omitted --><code>将Excel中的数据导入MySQL数据库</code><!-- raw HTML omitted --></p>
<p>这里为了方便操作数据库我集成了<code>mybatis-plus</code></p>
<h3 id="校验excel版本">校验Excel版本</h3>
<blockquote>
<p>该段代码可在：下文<code>完整代码——service——impl</code>中找到</p>
</blockquote>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-java" data-lang="java"><span style="display:flex;"><span><span style="color:#75715e">//校验上传的文件是否是Excel表格
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span><span style="color:#66d9ef">if</span> <span style="color:#f92672">(!</span>fileName<span style="color:#f92672">.</span><span style="color:#a6e22e">matches</span><span style="color:#f92672">(</span><span style="color:#e6db74">&#34;^.+\\.(?i)(xls)$&#34;</span><span style="color:#f92672">)</span> <span style="color:#f92672">&amp;&amp;</span> <span style="color:#f92672">!</span>fileName<span style="color:#f92672">.</span><span style="color:#a6e22e">matches</span><span style="color:#f92672">(</span><span style="color:#e6db74">&#34;^.+\\.(?i)(xlsx)$&#34;</span><span style="color:#f92672">))</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>    <span style="color:#66d9ef">throw</span> <span style="color:#66d9ef">new</span> BusinessException<span style="color:#f92672">(</span>CommonCodeMsg<span style="color:#f92672">.</span><span style="color:#a6e22e">IMPORT_TYPE_INVALID</span><span style="color:#f92672">);</span>
</span></span><span style="display:flex;"><span><span style="color:#f92672">}</span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">boolean</span> isExcel2003 <span style="color:#f92672">=</span> <span style="color:#66d9ef">true</span><span style="color:#f92672">;</span>
</span></span><span style="display:flex;"><span><span style="color:#75715e">//判断Excel版本
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span><span style="color:#66d9ef">if</span> <span style="color:#f92672">(</span>fileName<span style="color:#f92672">.</span><span style="color:#a6e22e">matches</span><span style="color:#f92672">(</span><span style="color:#e6db74">&#34;^.+\\.(?i)(xlsx)$&#34;</span><span style="color:#f92672">))</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>    isExcel2003 <span style="color:#f92672">=</span> <span style="color:#66d9ef">false</span><span style="color:#f92672">;</span>
</span></span><span style="display:flex;"><span><span style="color:#f92672">}</span>
</span></span><span style="display:flex;"><span>InputStream is <span style="color:#f92672">=</span> <span style="color:#66d9ef">null</span><span style="color:#f92672">;</span>
</span></span><span style="display:flex;"><span>Workbook wb <span style="color:#f92672">=</span> <span style="color:#66d9ef">null</span><span style="color:#f92672">;</span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">try</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>    is <span style="color:#f92672">=</span> file<span style="color:#f92672">.</span><span style="color:#a6e22e">getInputStream</span><span style="color:#f92672">();</span>
</span></span><span style="display:flex;"><span>    <span style="color:#66d9ef">if</span> <span style="color:#f92672">(</span>isExcel2003<span style="color:#f92672">)</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>        wb <span style="color:#f92672">=</span> <span style="color:#66d9ef">new</span> HSSFWorkbook<span style="color:#f92672">(</span>is<span style="color:#f92672">);</span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">}</span> <span style="color:#66d9ef">else</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>        wb <span style="color:#f92672">=</span> <span style="color:#66d9ef">new</span> XSSFWorkbook<span style="color:#f92672">(</span>is<span style="color:#f92672">);</span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">}</span>
</span></span><span style="display:flex;"><span><span style="color:#f92672">}</span> <span style="color:#66d9ef">catch</span> <span style="color:#f92672">(</span>IOException e<span style="color:#f92672">)</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>    e<span style="color:#f92672">.</span><span style="color:#a6e22e">printStackTrace</span><span style="color:#f92672">();</span>
</span></span><span style="display:flex;"><span><span style="color:#f92672">}</span>
</span></span></code></pre></div><h3 id="核心代码">核心代码</h3>
<blockquote>
<p>该段代码可在：下文<code>完整代码——service——impl</code>中找到</p>
</blockquote>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-java" data-lang="java"><span style="display:flex;"><span>Sheet sheet <span style="color:#f92672">=</span> wb<span style="color:#f92672">.</span><span style="color:#a6e22e">getSheetAt</span><span style="color:#f92672">(</span>0<span style="color:#f92672">);</span><span style="color:#75715e">//读取第一页
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span><span style="color:#66d9ef">int</span> lastRowNum <span style="color:#f92672">=</span> sheet<span style="color:#f92672">.</span><span style="color:#a6e22e">getLastRowNum</span><span style="color:#f92672">();</span> <span style="color:#75715e">//获取最后一行的索引
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span><span style="color:#66d9ef">for</span> <span style="color:#f92672">(</span><span style="color:#66d9ef">int</span> i <span style="color:#f92672">=</span> 1<span style="color:#f92672">;</span> i <span style="color:#f92672">&lt;=</span> lastRowNum<span style="color:#f92672">;</span> i<span style="color:#f92672">++)</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>    Row row <span style="color:#f92672">=</span> sheet<span style="color:#f92672">.</span><span style="color:#a6e22e">getRow</span><span style="color:#f92672">(</span>i<span style="color:#f92672">);</span>
</span></span><span style="display:flex;"><span>    row<span style="color:#f92672">.</span><span style="color:#a6e22e">getCell</span><span style="color:#f92672">(</span>0<span style="color:#f92672">).</span><span style="color:#a6e22e">setCellType</span><span style="color:#f92672">(</span>CellType<span style="color:#f92672">.</span><span style="color:#a6e22e">STRING</span><span style="color:#f92672">);</span>
</span></span><span style="display:flex;"><span>    String sno <span style="color:#f92672">=</span> row<span style="color:#f92672">.</span><span style="color:#a6e22e">getCell</span><span style="color:#f92672">(</span>0<span style="color:#f92672">).</span><span style="color:#a6e22e">getStringCellValue</span><span style="color:#f92672">();</span>
</span></span><span style="display:flex;"><span>    <span style="color:#66d9ef">if</span> <span style="color:#f92672">(</span>sno <span style="color:#960050;background-color:#1e0010">`</span> <span style="color:#66d9ef">null</span> <span style="color:#f92672">||</span> sno<span style="color:#f92672">.</span><span style="color:#a6e22e">equals</span><span style="color:#f92672">(</span><span style="color:#e6db74">&#34;&#34;</span><span style="color:#f92672">))</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>        <span style="color:#66d9ef">return</span> <span style="color:#e6db74">&#34;第&#34;</span><span style="color:#f92672">+</span> i<span style="color:#f92672">+</span><span style="color:#e6db74">&#34;行sno为空&#34;</span><span style="color:#f92672">;</span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">}</span>
</span></span><span style="display:flex;"><span>    String sname <span style="color:#f92672">=</span> row<span style="color:#f92672">.</span><span style="color:#a6e22e">getCell</span><span style="color:#f92672">(</span>1<span style="color:#f92672">).</span><span style="color:#a6e22e">getStringCellValue</span><span style="color:#f92672">();</span>
</span></span><span style="display:flex;"><span>    String ssex <span style="color:#f92672">=</span> row<span style="color:#f92672">.</span><span style="color:#a6e22e">getCell</span><span style="color:#f92672">(</span>2<span style="color:#f92672">).</span><span style="color:#a6e22e">getStringCellValue</span><span style="color:#f92672">();</span>
</span></span><span style="display:flex;"><span>    <span style="color:#66d9ef">int</span> sage <span style="color:#f92672">=</span> <span style="color:#f92672">(</span><span style="color:#66d9ef">int</span><span style="color:#f92672">)</span> row<span style="color:#f92672">.</span><span style="color:#a6e22e">getCell</span><span style="color:#f92672">(</span>3<span style="color:#f92672">).</span><span style="color:#a6e22e">getNumericCellValue</span><span style="color:#f92672">();</span>
</span></span><span style="display:flex;"><span>    String sdept <span style="color:#f92672">=</span> row<span style="color:#f92672">.</span><span style="color:#a6e22e">getCell</span><span style="color:#f92672">(</span>4<span style="color:#f92672">).</span><span style="color:#a6e22e">getStringCellValue</span><span style="color:#f92672">();</span>
</span></span><span style="display:flex;"><span>    Student student <span style="color:#f92672">=</span> <span style="color:#66d9ef">new</span> Student<span style="color:#f92672">();</span>
</span></span><span style="display:flex;"><span><span style="color:#f92672">}</span>
</span></span></code></pre></div><h3 id="完整代码">完整代码</h3>
<h4 id="数据库表">数据库表</h4>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">CREATE</span> <span style="color:#66d9ef">TABLE</span> <span style="color:#f92672">`</span>student<span style="color:#f92672">`</span> (
</span></span><span style="display:flex;"><span>  <span style="color:#f92672">`</span>sno<span style="color:#f92672">`</span> <span style="color:#66d9ef">char</span>(<span style="color:#ae81ff">4</span>) <span style="color:#66d9ef">NOT</span> <span style="color:#66d9ef">NULL</span>,
</span></span><span style="display:flex;"><span>  <span style="color:#f92672">`</span>sname<span style="color:#f92672">`</span> <span style="color:#66d9ef">char</span>(<span style="color:#ae81ff">8</span>) <span style="color:#66d9ef">DEFAULT</span> <span style="color:#66d9ef">NULL</span>,
</span></span><span style="display:flex;"><span>  <span style="color:#f92672">`</span>ssex<span style="color:#f92672">`</span> <span style="color:#66d9ef">char</span>(<span style="color:#ae81ff">2</span>) <span style="color:#66d9ef">DEFAULT</span> <span style="color:#66d9ef">NULL</span>,
</span></span><span style="display:flex;"><span>  <span style="color:#f92672">`</span>sage<span style="color:#f92672">`</span> <span style="color:#66d9ef">smallint</span> <span style="color:#66d9ef">DEFAULT</span> <span style="color:#66d9ef">NULL</span>,
</span></span><span style="display:flex;"><span>  <span style="color:#f92672">`</span>sdept<span style="color:#f92672">`</span> <span style="color:#66d9ef">char</span>(<span style="color:#ae81ff">20</span>) <span style="color:#66d9ef">DEFAULT</span> <span style="color:#66d9ef">NULL</span>,
</span></span><span style="display:flex;"><span>  <span style="color:#66d9ef">PRIMARY</span> <span style="color:#66d9ef">KEY</span> (<span style="color:#f92672">`</span>sno<span style="color:#f92672">`</span>),
</span></span><span style="display:flex;"><span>  <span style="color:#66d9ef">UNIQUE</span> <span style="color:#66d9ef">KEY</span> <span style="color:#f92672">`</span>stusname<span style="color:#f92672">`</span> (<span style="color:#f92672">`</span>sname<span style="color:#f92672">`</span>),
</span></span><span style="display:flex;"><span>  <span style="color:#66d9ef">CONSTRAINT</span> <span style="color:#f92672">`</span>student_chk_1<span style="color:#f92672">`</span> <span style="color:#66d9ef">CHECK</span> ((<span style="color:#f92672">`</span>ssex<span style="color:#f92672">`</span> <span style="color:#66d9ef">in</span> (<span style="color:#e6db74">_utf8mb4</span><span style="color:#e6db74">&#39;男&#39;</span>,<span style="color:#e6db74">_utf8mb4</span><span style="color:#e6db74">&#39;女&#39;</span>))),
</span></span><span style="display:flex;"><span>  <span style="color:#66d9ef">CONSTRAINT</span> <span style="color:#f92672">`</span>student_chk_2<span style="color:#f92672">`</span> <span style="color:#66d9ef">CHECK</span> (((<span style="color:#ae81ff">0</span> <span style="color:#f92672">&lt;</span> <span style="color:#f92672">`</span>sage<span style="color:#f92672">`</span>) <span style="color:#f92672">&lt;</span> <span style="color:#ae81ff">150</span>))
</span></span><span style="display:flex;"><span>) <span style="color:#66d9ef">ENGINE</span><span style="color:#f92672">=</span>InnoDB <span style="color:#66d9ef">DEFAULT</span> <span style="color:#66d9ef">CHARSET</span><span style="color:#f92672">=</span>utf8mb4 <span style="color:#66d9ef">COLLATE</span><span style="color:#f92672">=</span>utf8mb4_0900_ai_ci;
</span></span></code></pre></div><h4 id="pomxml">pom.xml</h4>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-xml" data-lang="xml"><span style="display:flex;"><span><span style="color:#75715e">&lt;?xml version=&#34;1.0&#34; encoding=&#34;UTF-8&#34;?&gt;</span>
</span></span><span style="display:flex;"><span><span style="color:#f92672">&lt;project</span> <span style="color:#a6e22e">xmlns=</span><span style="color:#e6db74">&#34;http://maven.apache.org/POM/4.0.0&#34;</span>
</span></span><span style="display:flex;"><span>         <span style="color:#a6e22e">xmlns:xsi=</span><span style="color:#e6db74">&#34;http://www.w3.org/2001/XMLSchema-instance&#34;</span>
</span></span><span style="display:flex;"><span>         <span style="color:#a6e22e">xsi:schemaLocation=</span><span style="color:#e6db74">&#34;http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd&#34;</span><span style="color:#f92672">&gt;</span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">&lt;modelVersion&gt;</span>4.0.0<span style="color:#f92672">&lt;/modelVersion&gt;</span>
</span></span><span style="display:flex;"><span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">&lt;groupId&gt;</span>cn.czh0123<span style="color:#f92672">&lt;/groupId&gt;</span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">&lt;artifactId&gt;</span>spring-boot-operating-Excel<span style="color:#f92672">&lt;/artifactId&gt;</span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">&lt;version&gt;</span>1.0.0<span style="color:#f92672">&lt;/version&gt;</span>
</span></span><span style="display:flex;"><span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">&lt;properties&gt;</span>
</span></span><span style="display:flex;"><span>        <span style="color:#f92672">&lt;maven.compiler.source&gt;</span>8<span style="color:#f92672">&lt;/maven.compiler.source&gt;</span>
</span></span><span style="display:flex;"><span>        <span style="color:#f92672">&lt;maven.compiler.target&gt;</span>8<span style="color:#f92672">&lt;/maven.compiler.target&gt;</span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">&lt;/properties&gt;</span>
</span></span><span style="display:flex;"><span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">&lt;parent&gt;</span>
</span></span><span style="display:flex;"><span>        <span style="color:#f92672">&lt;artifactId&gt;</span>spring-boot-starter-parent<span style="color:#f92672">&lt;/artifactId&gt;</span>
</span></span><span style="display:flex;"><span>        <span style="color:#f92672">&lt;groupId&gt;</span>org.springframework.boot<span style="color:#f92672">&lt;/groupId&gt;</span>
</span></span><span style="display:flex;"><span>        <span style="color:#f92672">&lt;version&gt;</span>2.3.2.RELEASE<span style="color:#f92672">&lt;/version&gt;</span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">&lt;/parent&gt;</span>
</span></span><span style="display:flex;"><span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">&lt;dependencies&gt;</span>
</span></span><span style="display:flex;"><span>        <span style="color:#f92672">&lt;dependency&gt;</span>
</span></span><span style="display:flex;"><span>            <span style="color:#f92672">&lt;groupId&gt;</span>org.springframework.boot<span style="color:#f92672">&lt;/groupId&gt;</span>
</span></span><span style="display:flex;"><span>            <span style="color:#f92672">&lt;artifactId&gt;</span>spring-boot-starter-web<span style="color:#f92672">&lt;/artifactId&gt;</span>
</span></span><span style="display:flex;"><span>        <span style="color:#f92672">&lt;/dependency&gt;</span>
</span></span><span style="display:flex;"><span>        <span style="color:#f92672">&lt;dependency&gt;</span>
</span></span><span style="display:flex;"><span>            <span style="color:#f92672">&lt;groupId&gt;</span>org.apache.poi<span style="color:#f92672">&lt;/groupId&gt;</span>
</span></span><span style="display:flex;"><span>            <span style="color:#f92672">&lt;artifactId&gt;</span>poi-ooxml<span style="color:#f92672">&lt;/artifactId&gt;</span>
</span></span><span style="display:flex;"><span>            <span style="color:#f92672">&lt;version&gt;</span>4.1.2<span style="color:#f92672">&lt;/version&gt;</span>
</span></span><span style="display:flex;"><span>            <span style="color:#f92672">&lt;scope&gt;</span>compile<span style="color:#f92672">&lt;/scope&gt;</span>
</span></span><span style="display:flex;"><span>        <span style="color:#f92672">&lt;/dependency&gt;</span>
</span></span><span style="display:flex;"><span>
</span></span><span style="display:flex;"><span>        <span style="color:#f92672">&lt;dependency&gt;</span>
</span></span><span style="display:flex;"><span>            <span style="color:#f92672">&lt;groupId&gt;</span>org.projectlombok<span style="color:#f92672">&lt;/groupId&gt;</span>
</span></span><span style="display:flex;"><span>            <span style="color:#f92672">&lt;artifactId&gt;</span>lombok<span style="color:#f92672">&lt;/artifactId&gt;</span>
</span></span><span style="display:flex;"><span>            <span style="color:#f92672">&lt;version&gt;</span>1.18.6<span style="color:#f92672">&lt;/version&gt;</span>
</span></span><span style="display:flex;"><span>            <span style="color:#f92672">&lt;optional&gt;</span>true<span style="color:#f92672">&lt;/optional&gt;</span>
</span></span><span style="display:flex;"><span>        <span style="color:#f92672">&lt;/dependency&gt;</span>
</span></span><span style="display:flex;"><span>        <span style="color:#f92672">&lt;dependency&gt;</span>
</span></span><span style="display:flex;"><span>            <span style="color:#f92672">&lt;groupId&gt;</span>mysql<span style="color:#f92672">&lt;/groupId&gt;</span>
</span></span><span style="display:flex;"><span>            <span style="color:#f92672">&lt;artifactId&gt;</span>mysql-connector-java<span style="color:#f92672">&lt;/artifactId&gt;</span>
</span></span><span style="display:flex;"><span>        <span style="color:#f92672">&lt;/dependency&gt;</span>
</span></span><span style="display:flex;"><span>        <span style="color:#f92672">&lt;dependency&gt;</span>
</span></span><span style="display:flex;"><span>            <span style="color:#f92672">&lt;groupId&gt;</span>com.baomidou<span style="color:#f92672">&lt;/groupId&gt;</span>
</span></span><span style="display:flex;"><span>            <span style="color:#f92672">&lt;artifactId&gt;</span>mybatis-plus-boot-starter<span style="color:#f92672">&lt;/artifactId&gt;</span>
</span></span><span style="display:flex;"><span>            <span style="color:#f92672">&lt;version&gt;</span>3.4.0<span style="color:#f92672">&lt;/version&gt;</span>
</span></span><span style="display:flex;"><span>        <span style="color:#f92672">&lt;/dependency&gt;</span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">&lt;/dependencies&gt;</span>
</span></span><span style="display:flex;"><span>
</span></span><span style="display:flex;"><span><span style="color:#f92672">&lt;/project&gt;</span>
</span></span></code></pre></div><h4 id="applicationyaml">application.yaml</h4>
<p>在<code>resource</code>包下创建一个文件<code>application.yaml</code>或<code>application.yml</code></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-yaml" data-lang="yaml"><span style="display:flex;"><span><span style="color:#f92672">spring</span>:
</span></span><span style="display:flex;"><span>  <span style="color:#f92672">datasource</span>:
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">url</span>: <span style="color:#ae81ff">jdbc:mysql://localhost:3306/你的数据库名?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;tinyInt1isBit=false</span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">username</span>: <span style="color:#ae81ff">你的用户名</span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">password</span>: <span style="color:#ae81ff">你的密码</span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">driver-class-name</span>: <span style="color:#ae81ff">com.mysql.cj.jdbc.Driver</span>
</span></span></code></pre></div><h4 id="spring-boot启动类">spring boot启动类</h4>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-java" data-lang="java"><span style="display:flex;"><span><span style="color:#a6e22e">@SpringBootApplication</span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">public</span> <span style="color:#66d9ef">class</span> <span style="color:#a6e22e">Run</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>    <span style="color:#66d9ef">public</span> <span style="color:#66d9ef">static</span> <span style="color:#66d9ef">void</span> <span style="color:#a6e22e">main</span><span style="color:#f92672">(</span>String<span style="color:#f92672">[]</span> args<span style="color:#f92672">)</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>        SpringApplication<span style="color:#f92672">.</span><span style="color:#a6e22e">run</span><span style="color:#f92672">(</span>Run<span style="color:#f92672">.</span><span style="color:#a6e22e">class</span><span style="color:#f92672">,</span> args<span style="color:#f92672">);</span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">}</span>
</span></span><span style="display:flex;"><span><span style="color:#f92672">}</span>
</span></span></code></pre></div><h4 id="domain">domain</h4>
<p>新建一个<code>domian</code>包，在该包下新建一个类<code>student</code></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-java" data-lang="java"><span style="display:flex;"><span><span style="color:#a6e22e">@Data</span>
</span></span><span style="display:flex;"><span><span style="color:#a6e22e">@AllArgsConstructor</span>
</span></span><span style="display:flex;"><span><span style="color:#a6e22e">@NoArgsConstructor</span>
</span></span><span style="display:flex;"><span><span style="color:#a6e22e">@TableName</span><span style="color:#f92672">(</span><span style="color:#e6db74">&#34;student&#34;</span><span style="color:#f92672">)</span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">public</span> <span style="color:#66d9ef">class</span> <span style="color:#a6e22e">Student</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>    <span style="color:#66d9ef">private</span> String sno<span style="color:#f92672">;</span>
</span></span><span style="display:flex;"><span>    <span style="color:#66d9ef">private</span> String sname<span style="color:#f92672">;</span>
</span></span><span style="display:flex;"><span>    <span style="color:#66d9ef">private</span> String ssex<span style="color:#f92672">;</span>
</span></span><span style="display:flex;"><span>    <span style="color:#66d9ef">private</span> Integer sage<span style="color:#f92672">;</span>
</span></span><span style="display:flex;"><span>    <span style="color:#66d9ef">private</span> String sdept<span style="color:#f92672">;</span>
</span></span><span style="display:flex;"><span><span style="color:#f92672">}</span>
</span></span></code></pre></div><h4 id="mapper">mapper</h4>
<p>新建一个<code>mapper</code>包，在该包下新建一个接口<code>StudentMapper</code></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-java" data-lang="java"><span style="display:flex;"><span><span style="color:#a6e22e">@Mapper</span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">public</span> <span style="color:#66d9ef">interface</span> <span style="color:#a6e22e">StudentMapper</span> <span style="color:#66d9ef">extends</span> BaseMapper<span style="color:#f92672">&lt;</span>Student<span style="color:#f92672">&gt;</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span><span style="color:#f92672">}</span>
</span></span></code></pre></div><h4 id="service">service</h4>
<p>新建一个<code>service</code>包，在该包下新建一个接口<code>IStudentService</code></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-java" data-lang="java"><span style="display:flex;"><span><span style="color:#66d9ef">public</span> <span style="color:#66d9ef">interface</span> <span style="color:#a6e22e">IStudentService</span> <span style="color:#66d9ef">extends</span> IService<span style="color:#f92672">&lt;</span>Student<span style="color:#f92672">&gt;</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>    String <span style="color:#a6e22e">importExcel</span><span style="color:#f92672">(</span>MultipartFile file<span style="color:#f92672">);</span>
</span></span><span style="display:flex;"><span><span style="color:#f92672">}</span>
</span></span></code></pre></div><h5 id="impl">impl</h5>
<p>在<code>service</code>包下新建一个包<code>impl</code>，在该包下新建一个实现类<code>StudentServiceImpl</code></p>
<ul>
<li>这里为了省事，只对<code>sno</code>做了参数校验</li>
</ul>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-java" data-lang="java"><span style="display:flex;"><span><span style="color:#a6e22e">@Service</span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">public</span> <span style="color:#66d9ef">class</span> <span style="color:#a6e22e">StudentServiceImpl</span> <span style="color:#66d9ef">extends</span> ServiceImpl<span style="color:#f92672">&lt;</span>StudentMapper<span style="color:#f92672">,</span> Student<span style="color:#f92672">&gt;</span> <span style="color:#66d9ef">implements</span> IStudentService <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>    <span style="color:#a6e22e">@Override</span>
</span></span><span style="display:flex;"><span>    <span style="color:#66d9ef">public</span> String <span style="color:#a6e22e">importExcel</span><span style="color:#f92672">(</span>MultipartFile file<span style="color:#f92672">)</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>        String fileName <span style="color:#f92672">=</span> file<span style="color:#f92672">.</span><span style="color:#a6e22e">getOriginalFilename</span><span style="color:#f92672">();</span>
</span></span><span style="display:flex;"><span>        <span style="color:#66d9ef">if</span> <span style="color:#f92672">(!</span>fileName<span style="color:#f92672">.</span><span style="color:#a6e22e">matches</span><span style="color:#f92672">(</span><span style="color:#e6db74">&#34;^.+\\.(?i)(xls)$&#34;</span><span style="color:#f92672">)</span> <span style="color:#f92672">&amp;&amp;</span> <span style="color:#f92672">!</span>fileName<span style="color:#f92672">.</span><span style="color:#a6e22e">matches</span><span style="color:#f92672">(</span><span style="color:#e6db74">&#34;^.+\\.(?i)(xlsx)$&#34;</span><span style="color:#f92672">))</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>            <span style="color:#66d9ef">return</span> <span style="color:#e6db74">&#34;文件上传格式不正确&#34;</span><span style="color:#f92672">;</span>
</span></span><span style="display:flex;"><span>        <span style="color:#f92672">}</span>
</span></span><span style="display:flex;"><span>        <span style="color:#66d9ef">boolean</span> isExcel2003 <span style="color:#f92672">=</span> <span style="color:#66d9ef">true</span><span style="color:#f92672">;</span>
</span></span><span style="display:flex;"><span>        <span style="color:#66d9ef">if</span> <span style="color:#f92672">(</span>fileName<span style="color:#f92672">.</span><span style="color:#a6e22e">matches</span><span style="color:#f92672">(</span><span style="color:#e6db74">&#34;^.+\\.(?i)(xlsx)$&#34;</span><span style="color:#f92672">))</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>            isExcel2003 <span style="color:#f92672">=</span> <span style="color:#66d9ef">false</span><span style="color:#f92672">;</span>
</span></span><span style="display:flex;"><span>        <span style="color:#f92672">}</span>
</span></span><span style="display:flex;"><span>        InputStream is <span style="color:#f92672">=</span> <span style="color:#66d9ef">null</span><span style="color:#f92672">;</span>
</span></span><span style="display:flex;"><span>        Workbook wb <span style="color:#f92672">=</span> <span style="color:#66d9ef">null</span><span style="color:#f92672">;</span>
</span></span><span style="display:flex;"><span>        <span style="color:#66d9ef">try</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>            is <span style="color:#f92672">=</span> file<span style="color:#f92672">.</span><span style="color:#a6e22e">getInputStream</span><span style="color:#f92672">();</span>
</span></span><span style="display:flex;"><span>            <span style="color:#66d9ef">if</span> <span style="color:#f92672">(</span>isExcel2003<span style="color:#f92672">)</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>                wb <span style="color:#f92672">=</span> <span style="color:#66d9ef">new</span> HSSFWorkbook<span style="color:#f92672">(</span>is<span style="color:#f92672">);</span>
</span></span><span style="display:flex;"><span>            <span style="color:#f92672">}</span> <span style="color:#66d9ef">else</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>                wb <span style="color:#f92672">=</span> <span style="color:#66d9ef">new</span> XSSFWorkbook<span style="color:#f92672">(</span>is<span style="color:#f92672">);</span>
</span></span><span style="display:flex;"><span>            <span style="color:#f92672">}</span>
</span></span><span style="display:flex;"><span>            Sheet sheet <span style="color:#f92672">=</span> wb<span style="color:#f92672">.</span><span style="color:#a6e22e">getSheetAt</span><span style="color:#f92672">(</span>0<span style="color:#f92672">);</span><span style="color:#75715e">//读取第一页
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>            <span style="color:#66d9ef">int</span> lastRowNum <span style="color:#f92672">=</span> sheet<span style="color:#f92672">.</span><span style="color:#a6e22e">getLastRowNum</span><span style="color:#f92672">();</span> <span style="color:#75715e">//获取最后一行的索引
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>            <span style="color:#66d9ef">for</span> <span style="color:#f92672">(</span><span style="color:#66d9ef">int</span> i <span style="color:#f92672">=</span> 1<span style="color:#f92672">;</span> i <span style="color:#f92672">&lt;=</span> lastRowNum<span style="color:#f92672">;</span> i<span style="color:#f92672">++)</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>                Row row <span style="color:#f92672">=</span> sheet<span style="color:#f92672">.</span><span style="color:#a6e22e">getRow</span><span style="color:#f92672">(</span>i<span style="color:#f92672">);</span>
</span></span><span style="display:flex;"><span>                row<span style="color:#f92672">.</span><span style="color:#a6e22e">getCell</span><span style="color:#f92672">(</span>0<span style="color:#f92672">).</span><span style="color:#a6e22e">setCellType</span><span style="color:#f92672">(</span>CellType<span style="color:#f92672">.</span><span style="color:#a6e22e">STRING</span><span style="color:#f92672">);</span>
</span></span><span style="display:flex;"><span>                String sno <span style="color:#f92672">=</span> row<span style="color:#f92672">.</span><span style="color:#a6e22e">getCell</span><span style="color:#f92672">(</span>0<span style="color:#f92672">).</span><span style="color:#a6e22e">getStringCellValue</span><span style="color:#f92672">();</span>
</span></span><span style="display:flex;"><span>                <span style="color:#66d9ef">if</span> <span style="color:#f92672">(</span>sno <span style="color:#960050;background-color:#1e0010">`</span> <span style="color:#66d9ef">null</span> <span style="color:#f92672">||</span> sno<span style="color:#f92672">.</span><span style="color:#a6e22e">equals</span><span style="color:#f92672">(</span><span style="color:#e6db74">&#34;&#34;</span><span style="color:#f92672">))</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>                    <span style="color:#66d9ef">return</span> <span style="color:#e6db74">&#34;第&#34;</span><span style="color:#f92672">+</span> i<span style="color:#f92672">+</span><span style="color:#e6db74">&#34;行sno为空&#34;</span><span style="color:#f92672">;</span> 
</span></span><span style="display:flex;"><span>                <span style="color:#f92672">}</span>
</span></span><span style="display:flex;"><span>                String sname <span style="color:#f92672">=</span> row<span style="color:#f92672">.</span><span style="color:#a6e22e">getCell</span><span style="color:#f92672">(</span>1<span style="color:#f92672">).</span><span style="color:#a6e22e">getStringCellValue</span><span style="color:#f92672">();</span>
</span></span><span style="display:flex;"><span>                String ssex <span style="color:#f92672">=</span> row<span style="color:#f92672">.</span><span style="color:#a6e22e">getCell</span><span style="color:#f92672">(</span>2<span style="color:#f92672">).</span><span style="color:#a6e22e">getStringCellValue</span><span style="color:#f92672">();</span>
</span></span><span style="display:flex;"><span>                <span style="color:#66d9ef">int</span> sage <span style="color:#f92672">=</span> <span style="color:#f92672">(</span><span style="color:#66d9ef">int</span><span style="color:#f92672">)</span> row<span style="color:#f92672">.</span><span style="color:#a6e22e">getCell</span><span style="color:#f92672">(</span>3<span style="color:#f92672">).</span><span style="color:#a6e22e">getNumericCellValue</span><span style="color:#f92672">();</span>
</span></span><span style="display:flex;"><span>                String sdept <span style="color:#f92672">=</span> row<span style="color:#f92672">.</span><span style="color:#a6e22e">getCell</span><span style="color:#f92672">(</span>4<span style="color:#f92672">).</span><span style="color:#a6e22e">getStringCellValue</span><span style="color:#f92672">();</span>
</span></span><span style="display:flex;"><span>                Student student <span style="color:#f92672">=</span> <span style="color:#66d9ef">new</span> Student<span style="color:#f92672">();</span>
</span></span><span style="display:flex;"><span>                student<span style="color:#f92672">.</span><span style="color:#a6e22e">setSno</span><span style="color:#f92672">(</span>sno<span style="color:#f92672">);</span>
</span></span><span style="display:flex;"><span>                student<span style="color:#f92672">.</span><span style="color:#a6e22e">setSname</span><span style="color:#f92672">(</span>sname<span style="color:#f92672">);</span>
</span></span><span style="display:flex;"><span>                student<span style="color:#f92672">.</span><span style="color:#a6e22e">setSsex</span><span style="color:#f92672">(</span>ssex<span style="color:#f92672">);</span>
</span></span><span style="display:flex;"><span>                student<span style="color:#f92672">.</span><span style="color:#a6e22e">setSage</span><span style="color:#f92672">(</span>sage<span style="color:#f92672">);</span>
</span></span><span style="display:flex;"><span>                student<span style="color:#f92672">.</span><span style="color:#a6e22e">setSdept</span><span style="color:#f92672">(</span>sdept<span style="color:#f92672">);</span>
</span></span><span style="display:flex;"><span>                System<span style="color:#f92672">.</span><span style="color:#a6e22e">err</span><span style="color:#f92672">.</span><span style="color:#a6e22e">println</span><span style="color:#f92672">(</span>student<span style="color:#f92672">);</span>
</span></span><span style="display:flex;"><span>                <span style="color:#66d9ef">boolean</span> isSuccess <span style="color:#f92672">=</span> <span style="color:#66d9ef">this</span><span style="color:#f92672">.</span><span style="color:#a6e22e">save</span><span style="color:#f92672">(</span>student<span style="color:#f92672">);</span>
</span></span><span style="display:flex;"><span>                <span style="color:#66d9ef">if</span> <span style="color:#f92672">(!</span>isSuccess<span style="color:#f92672">)</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>                    <span style="color:#66d9ef">return</span> <span style="color:#e6db74">&#34;导入失败&#34;</span><span style="color:#f92672">;</span>
</span></span><span style="display:flex;"><span>                <span style="color:#f92672">}</span>
</span></span><span style="display:flex;"><span>            <span style="color:#f92672">}</span>
</span></span><span style="display:flex;"><span>        <span style="color:#f92672">}</span> <span style="color:#66d9ef">catch</span> <span style="color:#f92672">(</span>IOException e<span style="color:#f92672">)</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>            e<span style="color:#f92672">.</span><span style="color:#a6e22e">printStackTrace</span><span style="color:#f92672">();</span>
</span></span><span style="display:flex;"><span>        <span style="color:#f92672">}</span>
</span></span><span style="display:flex;"><span>        <span style="color:#66d9ef">return</span> <span style="color:#e6db74">&#34;导入成功&#34;</span><span style="color:#f92672">;</span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">}</span>
</span></span><span style="display:flex;"><span><span style="color:#f92672">}</span>
</span></span></code></pre></div><h4 id="controller">controller</h4>
<p>新建一个<code>controller</code>包，在该包下新建一个接口<code>StudentController</code></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-java" data-lang="java"><span style="display:flex;"><span><span style="color:#a6e22e">@RestController</span>
</span></span><span style="display:flex;"><span><span style="color:#a6e22e">@RequestMapping</span><span style="color:#f92672">(</span><span style="color:#e6db74">&#34;/students&#34;</span><span style="color:#f92672">)</span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">public</span> <span style="color:#66d9ef">class</span> <span style="color:#a6e22e">StudentController</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>
</span></span><span style="display:flex;"><span>    <span style="color:#a6e22e">@Resource</span>
</span></span><span style="display:flex;"><span>    <span style="color:#66d9ef">private</span> IStudentService studentService<span style="color:#f92672">;</span>
</span></span><span style="display:flex;"><span>
</span></span><span style="display:flex;"><span>    <span style="color:#a6e22e">@PostMapping</span><span style="color:#f92672">(</span><span style="color:#e6db74">&#34;/importExcel&#34;</span><span style="color:#f92672">)</span>
</span></span><span style="display:flex;"><span>    <span style="color:#66d9ef">public</span> Object <span style="color:#a6e22e">importExcel</span><span style="color:#f92672">(</span><span style="color:#a6e22e">@RequestParam</span><span style="color:#f92672">(</span><span style="color:#e6db74">&#34;file&#34;</span><span style="color:#f92672">)</span> MultipartFile file<span style="color:#f92672">)</span> <span style="color:#f92672">{</span>
</span></span><span style="display:flex;"><span>        <span style="color:#66d9ef">return</span> studentService<span style="color:#f92672">.</span><span style="color:#a6e22e">importExcel</span><span style="color:#f92672">(</span>file<span style="color:#f92672">);</span>
</span></span><span style="display:flex;"><span>    <span style="color:#f92672">}</span>
</span></span><span style="display:flex;"><span><span style="color:#f92672">}</span>
</span></span></code></pre></div><h4 id="页面">页面</h4>
<p>在<code>resource</code>包下，新建一个<code>static</code>包，并且在该包下新建文件<code>index.html</code></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-html" data-lang="html"><span style="display:flex;"><span><span style="color:#75715e">&lt;!DOCTYPE html&gt;</span>
</span></span><span style="display:flex;"><span>&lt;<span style="color:#f92672">html</span> <span style="color:#a6e22e">lang</span><span style="color:#f92672">=</span><span style="color:#e6db74">&#34;en&#34;</span>&gt;
</span></span><span style="display:flex;"><span>&lt;<span style="color:#f92672">head</span>&gt;
</span></span><span style="display:flex;"><span>    &lt;<span style="color:#f92672">meta</span> <span style="color:#a6e22e">charset</span><span style="color:#f92672">=</span><span style="color:#e6db74">&#34;UTF-8&#34;</span>&gt;
</span></span><span style="display:flex;"><span>    &lt;<span style="color:#f92672">title</span>&gt;操作Excel&lt;/<span style="color:#f92672">title</span>&gt;
</span></span><span style="display:flex;"><span>&lt;/<span style="color:#f92672">head</span>&gt;
</span></span><span style="display:flex;"><span>&lt;<span style="color:#f92672">body</span>&gt;
</span></span><span style="display:flex;"><span>&lt;<span style="color:#f92672">form</span> <span style="color:#a6e22e">action</span><span style="color:#f92672">=</span><span style="color:#e6db74">&#34;/students/importExcel&#34;</span> <span style="color:#a6e22e">enctype</span><span style="color:#f92672">=</span><span style="color:#e6db74">&#34;multipart/form-data&#34;</span> <span style="color:#a6e22e">method</span><span style="color:#f92672">=</span><span style="color:#e6db74">&#34;post&#34;</span>&gt;
</span></span><span style="display:flex;"><span>    &lt;<span style="color:#f92672">input</span> <span style="color:#a6e22e">type</span><span style="color:#f92672">=</span><span style="color:#e6db74">&#34;file&#34;</span> <span style="color:#a6e22e">name</span><span style="color:#f92672">=</span><span style="color:#e6db74">&#34;file&#34;</span>&gt;
</span></span><span style="display:flex;"><span>    &lt;<span style="color:#f92672">input</span> <span style="color:#a6e22e">type</span><span style="color:#f92672">=</span><span style="color:#e6db74">&#34;submit&#34;</span> <span style="color:#a6e22e">value</span><span style="color:#f92672">=</span><span style="color:#e6db74">&#34;导入&#34;</span>&gt;
</span></span><span style="display:flex;"><span>&lt;/<span style="color:#f92672">form</span>&gt;
</span></span><span style="display:flex;"><span>&lt;/<span style="color:#f92672">body</span>&gt;
</span></span><span style="display:flex;"><span>&lt;/<span style="color:#f92672">html</span>&gt;
</span></span></code></pre></div><h3 id="运行">运行</h3>
<ul>
<li>启动Spring Boot项目</li>
</ul>
<hr>
<ul>
<li>新建一个Excel表格，如下图</li>
</ul>
<p><img src="https://img-blog.csdnimg.cn/fd8c342d1cca4736bedc7aee1909b017.png#pic_center" alt="Excel表格内容"></p>
<hr>
<ul>
<li>
<p>浏览器访问<code>localhost:8080/index.html</code>，如下图
<img src="https://img-blog.csdnimg.cn/d58b2ae177b44928b7242ed48fc99023.png#pic_center" alt="index.html"></p>
</li>
<li>
<p>选择需要上传的Excel点击导入，导入后控制会会输出以下数据
<img src="https://img-blog.csdnimg.cn/2ca4718d89614749813cd18ba13e2718.png#pic_center" alt="控制台输出"></p>
</li>
</ul>
<hr>
<ul>
<li>查看数据库是否添加成功
<img src="https://img-blog.csdnimg.cn/b930ce8a9e224057afe6e80f70c9380f.png#pic_center" alt="数据库">
由上图可知，Excel数据已成功导入数据库</li>
</ul>
<h2 id="实现导出功能">实现导出功能</h2>
<p>未完待续。。。</p>

    <h4><a href="https://czh-dev.gitee.io/czh-blog.gitee.io/">Back to Home</a></h4>
  </div>

  <div class="span3 bs-docs-sidebar" style="position:fixed;right: 40px;top: 50px;">
    <h1>catalogue</h1>
    <ul class="nav nav-list bs-docs-sidenav">
      <div class="toc-div">
        <nav id="TableOfContents">
  <ul>
    <li><a href="#apache-poi">Apache POI</a></li>
    <li><a href="#实现导入功能">实现导入功能</a>
      <ul>
        <li><a href="#校验excel版本">校验Excel版本</a></li>
        <li><a href="#核心代码">核心代码</a></li>
        <li><a href="#完整代码">完整代码</a></li>
        <li><a href="#运行">运行</a></li>
      </ul>
    </li>
    <li><a href="#实现导出功能">实现导出功能</a></li>
  </ul>
</nav>
      </div>
    </ul>
  </div>

</div>
<script src="https://cdn.jsdelivr.net/npm/gumshoejs@5.1.2/dist/gumshoe.min.js"></script>
<script>
  var spy = new Gumshoe('#TableOfContents a', {
    nested: true,
    nestedClass: 'active'
  });
</script>
<style>
   
  #TableOfContents li,
  #TableOfContents ul {
    list-style-type: none;
  }

  #TableOfContents ul {
    padding-left: 0px;
  }

  #TableOfContents li>a {
    display: block;
    padding: 4px 20px;
    font-size: 95%;
    color: #000000;
  }

  #TableOfContents li>a:hover,
  #TableOfContents li>a:focus {
    padding-left: 19px;
    color: #3A6bA5;
    text-decoration: none;
    background-color: transparent;
    border-left: 1px solid #3A6bA5;
  }

  #TableOfContents li.active>a,
  #TableOfContents li.active>a:hover,
  #TableOfContents li.active>a:focus {
    padding-left: 18px;
    font-weight: bold;
    color: #3A6bA5;
    background-color: transparent;
    border-left: 2px solid #3A6bA5;
  }

   
  #TableOfContents li>ul {
    padding-bottom: 10px;
  }

  #TableOfContents li li>a {
    padding-top: 1px;
    padding-bottom: 1px;
    padding-left: 30px;
    font-size: 14px;
    font-weight: normal;
  }

  #TableOfContents li li>a:hover,
  #TableOfContents li li>a:focus {
    padding-left: 29px;
  }

  #TableOfContents li li.active>a,
  #TableOfContents li li.active>a:hover,
  #TableOfContents li li.active>a:focus {
    padding-left: 28px;
    font-weight: 500;
  }

  #TableOfContents .nav-link.active+ul {
    display: block;
  }

  #TableOfContents li>ul {
    display: none;
  }

  #TableOfContents li.active>ul {
    display: inherit;
  }

  .toc-div {
    position: -webkit-sticky;
     
    position: sticky;
     
    top: 20px;
  }
</style>


        </div><footer class="container">
    <hr class="soften">
    <p>
    <a href="https://space.bilibili.com/1799809923">Love eating fried pork ribs</a> | 

&copy; 
<a href="http://jmf-portfolio.netlify.com" target="_blank">
    JM Fergeau
</a>
<span id="thisyear">2023</span>

    | My site


        | Built on <a href="//gohugo.io" target="_blank">Hugo</a>

</p>
    <p class="text-center">
        <a href="https://facebook.com">Facebook</a> 
        <a href="https://twitter.com">Twitter</a> 
        <a href="https://linkedin.com">Linkedin</a> 
        <a href="https://github.com">GitHub</a> 
        <a href="https://gitlab.com">GitLab</a>
    </p>
</footer>

</body><link rel="stylesheet" href="/czh-blog.gitee.io/css/bootstrap.css">
<link rel="stylesheet" href="/czh-blog.gitee.io/css/bootstrap-responsive.css">
<link rel="stylesheet" href="/czh-blog.gitee.io/css/style.css">

<script src="/czh-blog.gitee.io/js/jquery.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-386.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-transition.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-alert.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-modal.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-dropdown.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-scrollspy.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-tab.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-tooltip.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-popover.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-button.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-collapse.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-carousel.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-typeahead.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-affix.js"></script>
<script>
    _386 = { 
        fastLoad: false ,
        onePass: false , 
        speedFactor: 1 
    };

    
    function ThisYear() {
        document.getElementById('thisyear').innerHTML = new Date().getFullYear();
    };
</script>
</html>
